const express = require('express');
const bodyParser = require('body-parser');
const cors = require('cors');
const pool = require('./db');

const app = express();
app.use(cors());
app.use(bodyParser.json());

// 路由占位
app.get('/', (req, res) => {
  res.send('University API running');
});

// TODO: 添加学生、课程、好友、消息、转账等API路由

// 学生注册
app.post('/api/register', (req, res) => {
  const { student_id, name, birth_date, id_card, address, password } = req.body;
  if (!student_id || !id_card || !password) {
    return res.json({ success: false, message: '学号、身份证号、密码为必填项' });
  }
  pool.query('SELECT * FROM student WHERE student_id = ?', [student_id], (err, results) => {
    if (err) {
      console.error('注册前查询错误:', err);
      return res.json({ success: false, message: '数据库错误' });
    }
    if (results.length > 0) {
      return res.json({ success: false, message: '学号已存在' });
    }
    pool.query('INSERT INTO student (student_id, name, birth_date, id_card, address, password) VALUES (?, ?, ?, ?, ?, ?)',
      [student_id, name || '', birth_date || '', id_card, address || '', password],
      (err2) => {
        if (err2) {
          console.error('注册失败详细错误:', err2);
          return res.json({ success: false, message: '注册失败，数据库错误' });
        }
        res.json({ success: true });
      }
    );
  });
});

// 学生登录
app.post('/api/login', (req, res) => {
  const { student_id, password } = req.body;
  pool.query('SELECT * FROM student WHERE student_id = ? AND password = ?', [student_id, password], (err, results) => {
    if (err) return res.json({ success: false, message: '数据库错误' });
    if (results.length === 1) {
      // 登录成功，记录登录时间
      pool.query('INSERT INTO login_log (student_id, login_time) VALUES (?, NOW())', [student_id], (err2) => {
        if (err2) console.error('登录日志插入失败:', err2);
      });
      res.json({ success: true, user: results[0] });
    } else {
      res.json({ success: false, message: '学号或密码错误' });
    }
  });
});

// 管理员注册
app.post('/api/register-admin', (req, res) => {
  const { student_id, name, birth_date, id_card, address, password } = req.body;
  if (!student_id || !id_card || !password) {
    return res.json({ success: false, message: '学号、身份证号、密码为必填项' });
  }
  pool.query('SELECT * FROM student WHERE student_id = ?', [student_id], (err, results) => {
    if (err) return res.json({ success: false, message: '数据库错误' });
    if (results.length > 0) return res.json({ success: false, message: '学号已存在' });
    pool.query('INSERT INTO student (student_id, name, birth_date, id_card, address, password, is_admin) VALUES (?, ?, ?, ?, ?, ?, 1)',
      [student_id, name || '', birth_date || '', id_card, address || '', password],
      (err2) => {
        if (err2) return res.json({ success: false, message: '注册失败，数据库错误' });
        res.json({ success: true });
      }
    );
  });
});

// 管理员权限中间件
function requireAdmin(req, res, next) {
  const user = req.body._user || req.query._user;
  if (!user || !user.is_admin) {
    return res.json({ success: false, message: '无权限，管理员操作' });
  }
  next();
}

// 新增院系
app.post('/api/department', requireAdmin, (req, res) => {
  const { department_id, name } = req.body;
  if (!department_id || !name) {
    return res.json({ success: false, message: '信息不完整' });
  }
  pool.query('INSERT INTO department (department_id, name) VALUES (?, ?)', [department_id, name], (err) => {
    if (err) {
      return res.json({ success: false, message: '院系编号已存在' });
    }
    res.json({ success: true });
  });
});

// 获取所有院系
app.get('/api/department', (req, res) => {
  pool.query('SELECT * FROM department', (err, results) => {
    if (err) return res.json({ success: false, message: '数据库错误' });
    res.json({ success: true, data: results });
  });
});

// 新增课程
app.post('/api/course', requireAdmin, (req, res) => {
  const { course_id, name, department_id, credit } = req.body;
  if (!course_id || !name || !department_id || !credit) {
    return res.json({ success: false, message: '信息不完整' });
  }
  pool.query('INSERT INTO course (course_id, name, department_id, credit) VALUES (?, ?, ?, ?)', [course_id, name, department_id, credit], (err) => {
    if (err) {
      return res.json({ success: false, message: '课程号已存在或院系不存在' });
    }
    res.json({ success: true });
  });
});

// 获取所有课程
app.get('/api/course', (req, res) => {
  pool.query('SELECT c.*, d.name AS department_name FROM course c LEFT JOIN department d ON c.department_id = d.department_id', (err, results) => {
    if (err) return res.json({ success: false, message: '数据库错误' });
    res.json({ success: true, data: results });
  });
});

// 学生选课
app.post('/api/enroll', (req, res) => {
  const { student_id, course_id } = req.body;
  if (!student_id || !course_id) {
    return res.json({ success: false, message: '信息不完整' });
  }
  pool.query('INSERT INTO enrollment (student_id, course_id) VALUES (?, ?)', [student_id, course_id], (err) => {
    if (err) {
      return res.json({ success: false, message: '选课失败，可能已选或课程不存在' });
    }
    res.json({ success: true });
  });
});

// 查询学生已选课程
app.get('/api/enroll/:student_id', (req, res) => {
  const { student_id } = req.params;
  pool.query('SELECT e.*, c.name AS course_name, c.credit, c.department_id, d.name AS department_name, e.grade FROM enrollment e LEFT JOIN course c ON e.course_id = c.course_id LEFT JOIN department d ON c.department_id = d.department_id WHERE e.student_id = ?', [student_id], (err, results) => {
    if (err) return res.json({ success: false, message: '数据库错误' });
    res.json({ success: true, data: results });
  });
});

// 管理员录入成绩
app.post('/api/grade', (req, res) => {
  const { student_id, course_id, grade } = req.body;
  if (!student_id || !course_id || grade === undefined) {
    return res.json({ success: false, message: '信息不完整' });
  }
  pool.query('UPDATE enrollment SET grade = ? WHERE student_id = ? AND course_id = ?', [grade, student_id, course_id], (err, result) => {
    if (err || result.affectedRows === 0) {
      return res.json({ success: false, message: '录入失败，未找到该选课记录' });
    }
    res.json({ success: true });
  });
});

// 添加好友
app.post('/api/friend', (req, res) => {
  const { student_id1, student_id2 } = req.body;
  if (!student_id1 || !student_id2) {
    return res.json({ success: false, message: '信息不完整' });
  }
  const since_time = new Date();
  pool.query('INSERT INTO friendship (student_id1, student_id2, since_time) VALUES (?, ?, ?), (?, ?, ?)',
    [student_id1, student_id2, since_time, student_id2, student_id1, since_time], (err) => {
    if (err) {
      return res.json({ success: false, message: '添加失败，可能已是好友' });
    }
    res.json({ success: true });
  });
});

// 查询好友列表
app.get('/api/friend/:student_id', (req, res) => {
  const { student_id } = req.params;
  pool.query('SELECT f.student_id2 AS friend_id, s.name, f.since_time FROM friendship f LEFT JOIN student s ON f.student_id2 = s.student_id WHERE f.student_id1 = ?', [student_id], (err, results) => {
    if (err) return res.json({ success: false, message: '数据库错误' });
    res.json({ success: true, data: results });
  });
});

// 用户充值余额（仅普通用户）
app.post('/api/recharge', (req, res) => {
  const { student_id, amount } = req.body;
  if (!student_id || !amount) {
    return res.json({ success: false, message: '信息不完整' });
  }
  pool.query('SELECT is_admin FROM student WHERE student_id = ?', [student_id], (err, results) => {
    if (err || results.length === 0) return res.json({ success: false, message: '用户不存在' });
    if (results[0].is_admin) return res.json({ success: false, message: '管理员不能充值' });
    pool.query('UPDATE student SET balance = balance + ? WHERE student_id = ?', [amount, student_id], (err2) => {
      if (err2) return res.json({ success: false, message: '充值失败' });
      res.json({ success: true });
    });
  });
});

// 发起转账（含余额检查和预警）
app.post('/api/transfer', async (req, res) => {
  const { from_id, to_id, amount } = req.body;
  if (!from_id || !to_id || !amount) {
    return res.json({ success: false, message: '信息不完整' });
  }
  const transfer_time = new Date();
  try {
    // 检查是否管理员
    const [fromUser] = await new Promise((resolve, reject) => {
      pool.query('SELECT is_admin, balance FROM student WHERE student_id = ?', [from_id], (err, results) => {
        if (err) reject(err); else resolve([results[0]]);
      });
    });
    if (!fromUser || fromUser.is_admin) {
      return res.json({ success: false, message: '管理员不能转账' });
    }
    // 检查余额
    if (parseFloat(fromUser.balance) < parseFloat(amount)) {
      return res.json({ success: false, message: '余额不足' });
    }
    // 检查是否已在预警表
    const [warnRows] = await new Promise((resolve, reject) => {
      pool.query('SELECT * FROM warning WHERE student_id = ?', [from_id], (err, results) => {
        if (err) reject(err); else resolve([results]);
      });
    });
    if (warnRows.length > 0) {
      // 检查是否超过1分钟
      const lastWarning = warnRows.reduce((a, b) => new Date(a.trigger_time) > new Date(b.trigger_time) ? a : b);
      const now = new Date();
      const diffMs = now - new Date(lastWarning.trigger_time);
      if (diffMs < 60 * 1000) {
        return res.json({ success: false, message: '预警：该用户已被预警，操作受限！' });
      } else {
        // 超过1分钟，移除预警
        await new Promise((resolve, reject) => {
          pool.query('DELETE FROM warning WHERE student_id = ?', [from_id], (err) => {
            if (err) reject(err); else resolve();
          });
        });
      }
    }
    // 单笔转账超400预警
    if (parseFloat(amount) > 400) {
      pool.query('INSERT INTO warning (student_id, rule_type, trigger_time, total_amount) VALUES (?, 1, ?, ?)', [from_id, transfer_time, amount]);
    }
    // 累计转账超1000预警
    pool.query('SELECT SUM(amount) AS total FROM transfer WHERE from_id = ?', [from_id], (err, results) => {
      if (!err && results && results[0] && parseFloat(results[0].total || 0) + parseFloat(amount) > 1000) {
        pool.query('INSERT INTO warning (student_id, rule_type, trigger_time, total_amount) VALUES (?, 2, ?, ?)', [from_id, transfer_time, parseFloat(results[0].total || 0) + parseFloat(amount)]);
      }
    });
    // 正常插入转账并更新余额
    pool.query('INSERT INTO transfer (from_id, to_id, amount, transfer_time) VALUES (?, ?, ?, ?)', [from_id, to_id, amount, transfer_time], (err) => {
      if (err) {
        return res.json({ success: false, message: '转账失败' });
      }
      // 扣减转出方余额，增加收款方余额（收款方不是管理员）
      pool.query('UPDATE student SET balance = balance - ? WHERE student_id = ?', [amount, from_id]);
      pool.query('SELECT is_admin FROM student WHERE student_id = ?', [to_id], (err2, results2) => {
        if (!err2 && results2.length > 0 && !results2[0].is_admin) {
          pool.query('UPDATE student SET balance = balance + ? WHERE student_id = ?', [amount, to_id]);
        }
      });
      res.json({ success: true });
    });
  } catch (err) {
    return res.json({ success: false, message: '数据库错误' });
  }
});

// 查询转账记录
app.get('/api/transfer/:student_id', (req, res) => {
  const { student_id } = req.params;
  pool.query('SELECT * FROM transfer WHERE from_id = ? OR to_id = ? ORDER BY transfer_time DESC', [student_id, student_id], (err, results) => {
    if (err) return res.json({ success: false, message: '数据库错误' });
    res.json({ success: true, data: results });
  });
});

// 发送消息
app.post('/api/message', (req, res) => {
  const { from_id, to_id, content } = req.body;
  if (!from_id || !to_id || !content) {
    return res.json({ success: false, message: '信息不完整' });
  }
  const send_time = new Date();
  pool.query('INSERT INTO message (from_id, to_id, content, send_time) VALUES (?, ?, ?, ?)', [from_id, to_id, content, send_time], (err) => {
    if (err) {
      return res.json({ success: false, message: '发送失败' });
    }
    res.json({ success: true });
  });
});

// 查询消息记录
app.get('/api/message/:student_id/:friend_id', (req, res) => {
  const { student_id, friend_id } = req.params;
  pool.query('SELECT * FROM message WHERE (from_id = ? AND to_id = ?) OR (from_id = ? AND to_id = ?) ORDER BY send_time ASC', [student_id, friend_id, friend_id, student_id], (err, results) => {
    if (err) return res.json({ success: false, message: '数据库错误' });
    res.json({ success: true, data: results });
  });
});

// 获取所有预警信息（管理员用）
app.get('/api/warning', (req, res) => {
  pool.query('SELECT * FROM warning ORDER BY trigger_time DESC', (err, results) => {
    if (err) return res.json({ success: false, message: '数据库错误' });
    res.json({ success: true, data: results });
  });
});

// 获取当前用户余额
app.get('/api/balance/:student_id', (req, res) => {
  const student_id = req.params.student_id;
  pool.query('SELECT balance, is_admin FROM student WHERE student_id = ?', [student_id], (err, results) => {
    if (err || results.length === 0) return res.json({ success: false, message: '用户不存在' });
    if (results[0].is_admin) return res.json({ success: false, message: '管理员无余额' });
    res.json({ success: true, balance: results[0].balance });
  });
});

const PORT = process.env.PORT || 3001;
app.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`);
}); 